package com.tz.tzgw.pc.web.dao;

import com.tz.tzgw.pc.web.model.Article;
import com.tz.tzgw.pc.web.model.Banner;
import com.tz.tzgw.pc.web.model.Member;
import com.tz.tzgw.pc.web.model.PageResult;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import java.util.ArrayList;
import java.util.List;

public class ArticleDao {
    private final JdbcTemplate jdbcTemplate;

    public ArticleDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    public PageResult<Article> getArticlesByPageAndCondition(int pageNum, int pageSize, String title, Long columnId,String order) {
        PageResult<Article> pageResult = new PageResult<>();
        int offset = (pageNum - 1) * pageSize;

        // 构建查询总条数的 SQL
        StringBuilder countSqlBuilder = new StringBuilder("SELECT COUNT(*) FROM tz_article a");
        List<Object> countParams = new ArrayList<>();

        // 构建分页查询的 SQL
        StringBuilder querySqlBuilder = new StringBuilder("SELECT a.id, a.columnid, a.title, c.name as columnname, a.author, a.resource, a.thumb, a.video, a.hit, a.istop, a.istui, a.status, a.create_at FROM tz_article a");
        List<Object> queryParams = new ArrayList<>();

        // 判断 title 是否为空，并构建相应的查询条件
        if (title != null && !title.isEmpty()) {
            countSqlBuilder.append(" WHERE a.title LIKE ?");
            querySqlBuilder.append(" LEFT JOIN tz_webcolumn c ON a.columnid = c.id WHERE a.title LIKE ?");
            countParams.add("%" + title + "%");
            queryParams.add("%" + title + "%");
        } else {
            querySqlBuilder.append(" LEFT JOIN tz_webcolumn c ON a.columnid = c.id");
        }

        // 判断 columnId 是否为空，并构建相应的查询条件
        if (columnId != null) {
            if (title != null && !title.isEmpty()) {
                countSqlBuilder.append(" AND a.columnid = ?");
                querySqlBuilder.append(" AND a.columnid = ?");
            } else {
                countSqlBuilder.append(" WHERE a.columnid = ?");
                querySqlBuilder.append(" WHERE a.columnid = ?");
            }
            countParams.add(columnId);
            queryParams.add(columnId);
        }

        // 查询总条数
        String countSql = countSqlBuilder.toString();
        int total = jdbcTemplate.queryForObject(countSql, Integer.class, countParams.toArray());
        pageResult.setTotal(total);

        String orderStr = " ORDER BY ";

        if (order == "sort_order"){
            orderStr += " a.istop DESC,a.istui DESC, a.sort_order DESC LIMIT ?, ?";
        }else {
            orderStr += " a.id DESC LIMIT ?, ?";
        }

        // 分页查询
        querySqlBuilder.append(orderStr);
        queryParams.add(offset);
        queryParams.add(pageSize);

        String querySql = querySqlBuilder.toString();
        List<Article> articles = jdbcTemplate.query(querySql, new BeanPropertyRowMapper<>(Article.class), queryParams.toArray());
        pageResult.setData(articles);

        return pageResult;
    }

    public Article getArticleById(Long id) {
        String sql = "SELECT * FROM tz_article WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new BeanPropertyRowMapper<>(Article.class));
    }

    public Article getPrevArticleById(Long articleId) {
        String sql = "SELECT * FROM tz_article WHERE id < ? ORDER BY id DESC LIMIT 1";
        List<Article> articles = jdbcTemplate.query(sql, new Object[]{articleId}, new BeanPropertyRowMapper<>(Article.class));
        if (articles.isEmpty()) {
            return null;
        }
        return articles.get(0);
    }

    public Article getNextArticleById(Long articleId) {
        String sql = "SELECT * FROM tz_article WHERE id > ? ORDER BY id ASC LIMIT 1";
        List<Article> articles = jdbcTemplate.query(sql, new Object[]{articleId}, new BeanPropertyRowMapper<>(Article.class));
        if (articles.isEmpty()) {
            return null;
        }
        return articles.get(0);
    }

    public int insertOrUpdateArticle(Article article) {
        Long id = article.getId();
        if (id != null) {
            // Update
            String sql = "UPDATE tz_article SET ";
            List<String> updateFields = new ArrayList<>();
            List<Object> params = new ArrayList<>();

            if (article.getTitle() != null) {
                updateFields.add("title = ?");
                params.add(article.getTitle());
            }
            if (article.getColumnid() != null) {
                updateFields.add("columnid = ?");
                params.add(article.getColumnid());
            }
            if (article.getAuthor() != null) {
                updateFields.add("author = ?");
                params.add(article.getAuthor());
            }
            if (article.getResource() != null) {
                updateFields.add("resource = ?");
                params.add(article.getResource());
            }
            if (article.getThumb() != null) {
                updateFields.add("thumb = ?");
                params.add(article.getThumb());
            }
            if (article.getVideo() != null) {
                updateFields.add("video = ?");
                params.add(article.getVideo());
            }
            if (article.getContent() != null) {
                updateFields.add("content = ?");
                params.add(article.getContent());
            }
            if (article.getHit() != null) {
                updateFields.add("hit = ?");
                params.add(article.getHit());
            }
            if (article.getIstop() != null) {
                updateFields.add("istop = ?");
                params.add(article.getIstop());
            }
            if (article.getIstui() != null) {
                updateFields.add("istui = ?");
                params.add(article.getIstui());
            }
            if (article.getStatus() != null) {
                updateFields.add("status = ?");
                params.add(article.getStatus());
            }

            if (article.getSort_order() != null) {
                updateFields.add("sort_order = ?");
                params.add(article.getSort_order());
            }

            if (article.getCreate_at() != null) {
                updateFields.add("create_at = ?");
                params.add(article.getCreate_at());
            }

            sql += String.join(", ", updateFields);
            sql += " WHERE id = ?";

// 添加 id 参数
            params.add(id);

            jdbcTemplate.update(sql, params.toArray());
        } else {
            // Insert
            String sql = "INSERT INTO tz_article (title, columnid, author, resource, thumb, video, content, hit, istop, istui,status,sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            String customCreateAt = null;
            if (article.getCreate_at() != null) {
                customCreateAt = article.getCreate_at();
            }

            Object[] params = new Object[12];

            if (customCreateAt != null) {
                params = new Object[13];
                sql = "INSERT INTO tz_article (title, columnid, author, resource, thumb, video, content, hit, istop, istui,status,sort_order,create_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
                params[12] = customCreateAt;
            }
            params[0] = article.getTitle();
            params[1] = article.getColumnid();
            params[2] = article.getAuthor();
            params[3] = article.getResource();
            params[4] = article.getThumb();
            params[5] = article.getVideo();
            params[6] = article.getContent();
            params[7] = article.getHit();
            params[8] = article.getIstop();
            params[9] = article.getIstui();
            params[10] = article.getStatus();
            params[11] = article.getSort_order();

            jdbcTemplate.update(sql, params);
        }
        return 1;
    }


    public List<Article> getAllArticleByColumn(Long columnId) {
        String sql = "SELECT id,title,columnid,thumb FROM tz_article WHERE status=1 AND columnid=? ORDER BY create_at desc";
        return jdbcTemplate.query(sql, new Object[]{columnId},new BeanPropertyRowMapper<>(Article.class));
    }

    public List<Article> getAllArticleByIstui(Integer istui,Integer limit) {
        String sql = "SELECT id,title,columnid,thumb FROM tz_article WHERE status=1 AND istui=? ORDER BY create_at desc LIMIT ?";
        return jdbcTemplate.query(sql,new Object[]{istui,limit}, new BeanPropertyRowMapper<>(Article.class));
    }


    public Article getArticleByTopOne(Long columnid) {
        String sql = "SELECT * FROM tz_article WHERE status = 1 AND istop = 2 AND columnid=?  ORDER BY create_at DESC LIMIT 1";
        try {
            return jdbcTemplate.queryForObject(sql,new Object[]{columnid}, new BeanPropertyRowMapper<>(Article.class));
        } catch (EmptyResultDataAccessException e) {
            sql = "SELECT * FROM tz_article WHERE status = 1 AND columnid=? ORDER BY create_at DESC LIMIT 1";
            try {
                return jdbcTemplate.queryForObject(sql,new Object[]{columnid}, new BeanPropertyRowMapper<>(Article.class));
            } catch (EmptyResultDataAccessException ex) {
                return null;
            }
        }
    }


    public PageResult<Article> getArticlesByPageTopCondition(int pageNum, int pageSize, Long columnid, Integer istop) {
        PageResult<Article> pageResult = new PageResult<>();
        int offset = (pageNum - 1) * pageSize;

        // 构建查询总条数的 SQL
        StringBuilder countSqlBuilder = new StringBuilder("SELECT COUNT(*) FROM tz_article WHERE status = 1 ");
        List<Object> countParams = new ArrayList<>();

        // 构建分页查询的 SQL
        StringBuilder querySqlBuilder = new StringBuilder("SELECT id, title, columnid, author, resource, thumb, video, content, hit, istop, istui,status,sort_order,create_at FROM tz_article WHERE status = 1 ");
        List<Object> queryParams = new ArrayList<>();

        countSqlBuilder.append(" AND columnid=?");
        countParams.add(columnid);
        if (istop > 0){
            countSqlBuilder.append(" AND istop=?");
            countParams.add(istop);
        }

        // 查询总条数
        String countSql = countSqlBuilder.toString();
        int total = jdbcTemplate.queryForObject(countSql, Integer.class, countParams.toArray());
        pageResult.setTotal(total);


        querySqlBuilder.append(" AND columnid=?");
        queryParams.add(columnid);
        if (istop > 0) {
            querySqlBuilder.append(" AND istop=?");
            queryParams.add(istop);
        }

        // 分页查询
        querySqlBuilder.append(" ORDER BY istop DESC,istui DESC, sort_order DESC LIMIT ?, ?");
        queryParams.add(offset);
        queryParams.add(pageSize);

        String querySql = querySqlBuilder.toString();
        List<Article> articles = jdbcTemplate.query(querySql, new BeanPropertyRowMapper<>(Article.class), queryParams.toArray());
        pageResult.setData(articles);

        return pageResult;
    }

    public void incrementField(Long id, String fieldName) {
        String sql = "UPDATE tz_article SET " + fieldName + " = " + fieldName + " + 1 WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }


    public Integer deleteArticle(Integer id) {
        String sql = "DELETE FROM tz_article WHERE id = ?";
        jdbcTemplate.update(sql, id);
        return 1;
    }
}
